package com.matt.repository.impl;

import com.matt.domain.Children;
import com.matt.foundation.DO.AdviseDetailDO;
import com.matt.foundation.DO.ChildrenListDO;
import com.matt.repository.base.BaseNativeSqlRepository;
import org.springframework.stereotype.Service;
import com.matt.repository.base.ClassesChildrenRepository;

import java.util.Arrays;
import java.util.List;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2017/7/11
 * *******************************
 */
@Service
public class ClassesChildrenRepositoryImpl extends BaseNativeSqlRepository implements ClassesChildrenRepository {

    @Override
    public List<Object[]> findChildrenByClasses(String classesId) {
        String findChildrenSql = String.format("select DISTINCT c.id AS id, c.name AS childrenName, c.gender as gender, c.imgUrl as imgUrl, c.status as status"
                        + " from children c LEFT join rel_classes_children cc on cc.childrenId = c.id  WHERE cc.classesId='%s' ",
                classesId);
        List<Object[]> objectArrayList = sqlArrayList(findChildrenSql);
        return objectArrayList;
    }

    @Override
    public Children findAnyChildrenByClassesAndName(String classesId, String name) {
        String findChildrenSql = "select c.id AS id, c.name AS name, c.gender as gender, c.imgUrl as imgUrl , c.birthday as birthday " +
                " from children c " +
                "LEFT JOIN `rel_classes_children` rcc on rcc.`childrenId` = c.id " +
                "where rcc.`classesId` = :1 and c.deleted = false and c.name like concat('%', :2,'%') limit 1  ";

        List<Children> result = sqlObjectList(findChildrenSql, Arrays.asList(classesId, name), Children.class);
        if (result != null && result.size() > 0){
            return result.get(0);
        }
        return null;
    }

    @Override
    public List<ChildrenListDO> findChildrenByClassesTypeAndArea(Integer category, String areaId) {
        String findChildrenSql = String.format("select DISTINCT c.id AS id, c.name AS name, c.gender as gender, c.imgUrl as imgUrl"
                + " from children c LEFT JOIN rel_classes_children cc on cc.childrenId = c.id LEFT JOIN classes cl on cc.classesId = cl.id WHERE  cl.areaId='%s' and cl.category = '%s'", areaId, category);
        List<ChildrenListDO> objectArrayList = sqlObjectList(findChildrenSql, ChildrenListDO.class);
        return objectArrayList;
    }


    @Override
    public List<Object[]> findChildrenByClassesAndNurseryStatus(String classesId) {
        String findChildrenSql = String.format("select DISTINCT c.id AS id, c.name AS childrenName, c.gender as gender, c.imgUrl as imgUrl, c.status as status" +
                " from children c LEFT JOIN children_extend_info i on c.id = i.childrenId LEFT join rel_classes_children cc on cc.childrenId = c.id  " +
                " WHERE cc.classesId='%s' and c.isDisplayForAdmin = false and i.nurseryStatus > 0 ", classesId);
        List<Object[]> objectArrayList = sqlArrayList(findChildrenSql);
        return objectArrayList;
    }

    @Override
    public List<AdviseDetailDO> findAdviseByClasses(String classesId) {
        String querySql =  String.format("select a.childrenName as name , a.content as content, c.imgUrl as imgUrl" +
                        " from rel_classes_children cc right join garden_leader_advise a on cc.childrenId = a.childrenId left join children c on a.childrenId = c.id " +
                        " WHERE cc.classesId = '%s' and a.content !=''",
                classesId);
        List<AdviseDetailDO> objectArrayList = sqlObjectList(querySql, AdviseDetailDO.class);
        return objectArrayList;
    }

    @Override
    public Long sumNurseryLimitCount(String areaId) {
        String querySql = String.format("select sum(limitNum) as count from classes " +
                "  WHERE areaId = '%s'", areaId);
        Long sum = sqlSingleResult(querySql);
        return sum;
    }
    
    @Override
    public List<Object[]> findClassHourChildrenByClasses(String classesId) {
        String findChildrenSql = String.format("select DISTINCT c.id AS id, c.name AS childrenName, c.gender as gender, c.imgUrl as imgUrl, c.status as status,ce.earlyLeftCourseNum as earlyLeftCourseNum"
                        + " from children c "
                        + "LEFT join rel_classes_children cc on cc.childrenId = c.id  "
                        + "LEFT join children_extend_info ce on ce.childrenId = c.id  "
                        + "WHERE cc.classesId='%s'",
                classesId);
        List<Object[]> objectArrayList = sqlArrayList(findChildrenSql);
        return objectArrayList;
    }
}
